package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.joda.time.DateTime;


public class RemoteDatabase {
	
	Connection conn = null;
	
	public RemoteDatabase() {
	
		try {
			prepareConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public void prepareConnection() throws Exception {
		
		String userName = "produccion_postres";
		String password = "postres";
		String url = "jdbc:sqlserver://186.114.132.94:1677;databaseName=MGPOSTRES;";
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		//net.sourceforge.jtds.jdbc.Driver
		DriverManager.setLoginTimeout(1);
		conn = DriverManager.getConnection(url, userName, password);
		
	}
	
	
	public HashMap<Integer,HashMap<Integer,Integer>> constructPedidos(String dia, ArrayList<Integer> codigos) {

		
		String basequery = "SELECT e.ID as parentid, childid, e.DESCRIPCION, NUMPEDIDO, trimmedserie, CODCLIENTE, REFERENCIA, UNIDADESTOTAL, UNIDADESPEN "
				+"from (select c.IDPARENT as parentid, c.ID as childid, a.NUMPEDIDO , LEFT(c.SERIE,3) as trimmedserie, a.CODCLIENTE, "
				+"b.REFERENCIA, b.UNIDADESPEN, b.UNIDADESTOTAL from PEDVENTACAB as a, PEDVENTALIN as b, SERIES as c "
				+"WHERE (a.FECHAPEDIDO = '"+ dia +"') " 
				+"AND a.NUMPEDIDO = b.NUMPEDIDO "
				+"AND a.NUMSERIE = b.NUMSERIE "
				+"AND a.N = b.N "
				+"AND c.SERIE = a.NUMSERIE) as newtable "
				+"LEFT JOIN SERIES as d "
				+"ON newtable.parentid = d.ID "
				+"LEFT JOIN SERIES as e "
				+"ON d.IDPARENT = e.ID";
		
		if(codigos.size()!=0) 
			basequery += " WHERE ";
		else 
			basequery += " WHERE REFERENCIA = 0";
		
		
		for(int codigo: codigos) {
			basequery += " REFERENCIA = " + codigo + " OR ";
		}
		
		String query;
		if(basequery.endsWith("OR ")) {
			query = basequery.substring(0, basequery.length()-4);
		}
		else {
			query = basequery;
		}

		HashMap<Integer,HashMap<Integer,Integer>> mapaUnidadesPendiente = new HashMap<Integer,HashMap<Integer,Integer>>();
		
		for(int codigo: codigos) {
			mapaUnidadesPendiente.put(codigo, new HashMap<Integer,Integer>());
			for(int i =1; i< 8; i++) {
				mapaUnidadesPendiente.get(codigo).put(i, 0);
			}
		}

		try {

				//prepareConnection();

				Statement s = conn.createStatement();
				
				//System.out.println(query);
				s.executeQuery(query);
				

				ResultSet rs = s.getResultSet();

				while (rs.next()) {

					int unidades = rs.getInt("UNIDADESTOTAL");
					int codigoArticulo = rs.getInt("REFERENCIA");
					int codigoCliente = rs.getInt("CODCLIENTE");
					String serie = rs.getString("trimmedserie");
					int codigoPuntoDeVenta = 0;
					
					if(codigoCliente> 6) { 
						codigoPuntoDeVenta = (Integer.valueOf(serie) - 101)/10;
					}
					else {
						codigoPuntoDeVenta = codigoCliente;
					}
					
					mapaUnidadesPendiente.get(codigoArticulo).put(codigoPuntoDeVenta, unidades);

				}
				rs.close();
				s.close();

			} catch (Exception e) {
				System.err.println("Cannot connect to database server");
				e.printStackTrace();
			} finally {
				//closeConnection();
			}

			return mapaUnidadesPendiente;
		
		
	}
}
